// check_purchase_data.js
const { Sequelize, DataTypes } = require('sequelize');

// 数据库配置
const config = {
  dialect: 'mysql',
  host: '127.0.0.1',
  port: 3306,
  database: 'lingxi_system',
  username: 'root',
  password: '888mei525',
  timezone: '+08:00',
  define: {
    charset: 'utf8mb4',
    dialectOptions: {
      collate: 'utf8mb4_unicode_ci',
    },
    timestamps: true,
  },
};

// 创建Sequelize实例
const sequelize = new Sequelize(config.database, config.username, config.password, config);

// 定义采购订单模型
const PurchaseOrder = sequelize.define('purchase_order', {
  id: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true
  },
  orderNumber: {
    type: DataTypes.STRING(50),
    allowNull: false,
    unique: true,
    field: 'order_number'
  },
  supplierId: {
    type: DataTypes.INTEGER,
    allowNull: false,
    field: 'supplier_id'
  },
  shopName: {
    type: DataTypes.STRING(200),
    allowNull: true,
    field: 'shop_name'
  },
  purchaser: {
    type: DataTypes.STRING(50),
    allowNull: true
  },
  orderDate: {
    type: DataTypes.DATE,
    allowNull: false,
    field: 'order_date'
  },
  expectedDeliveryDate: {
    type: DataTypes.DATE,
    allowNull: true,
    field: 'expected_delivery_date'
  },
  totalAmount: {
    type: DataTypes.DECIMAL(15, 2),
    allowNull: false,
    field: 'total_amount'
  },
  status: {
    type: DataTypes.STRING(20),
    defaultValue: 'pending',
    allowNull: false
  },
  remark: {
    type: DataTypes.TEXT,
    allowNull: true
  },
  organizationId: {
    type: DataTypes.INTEGER,
    allowNull: false,
    field: 'organization_id'
  }
}, {
  tableName: 'purchase_orders',
  timestamps: true,
  createdAt: 'created_at',
  updatedAt: 'updated_at'
});

// 定义采购订单项模型
const PurchaseOrderItem = sequelize.define('purchase_order_item', {
  id: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true
  },
  orderId: {
    type: DataTypes.INTEGER,
    allowNull: false,
    field: 'order_id'
  },
  productId: {
    type: DataTypes.INTEGER,
    allowNull: true,
    field: 'product_id'
  },
  productName: {
    type: DataTypes.STRING(100),
    allowNull: false,
    field: 'product_name'
  },
  specification: {
    type: DataTypes.STRING(100),
    allowNull: true
  },
  quantity: {
    type: DataTypes.INTEGER,
    allowNull: false
  },
  unit: {
    type: DataTypes.STRING(20),
    allowNull: true
  },
  unitPrice: {
    type: DataTypes.DECIMAL(15, 2),
    allowNull: false,
    field: 'unit_price'
  },
  amount: {
    type: DataTypes.DECIMAL(15, 2),
    allowNull: false
  },
  remark: {
    type: DataTypes.STRING(200),
    allowNull: true
  }
}, {
  tableName: 'purchase_order_items',
  timestamps: true,
  createdAt: 'created_at',
  updatedAt: 'updated_at'
});

// 建立关联关系
PurchaseOrder.hasMany(PurchaseOrderItem, { foreignKey: 'orderId', as: 'items' });
PurchaseOrderItem.belongsTo(PurchaseOrder, { foreignKey: 'orderId', as: 'order' });

// 查询数据
async function checkPurchaseData() {
  try {
    // 同步数据库模型
    await sequelize.authenticate();
    console.log('数据库连接成功');
    
    // 查询采购订单数据
    console.log('\n=== 采购订单数据 ===');
    const orders = await PurchaseOrder.findAll({
      order: [['created_at', 'DESC']],
      limit: 5
    });
    
    console.log(`找到 ${orders.length} 条采购订单记录:`);
    orders.forEach((order, index) => {
      console.log(`\n订单 ${index + 1}:`);
      console.log(`  订单号: ${order.orderNumber}`);
      console.log(`  供应商ID: ${order.supplierId}`);
      console.log(`  店铺名称: ${order.shopName}`);
      console.log(`  采购员: ${order.purchaser}`);
      console.log(`  订单日期: ${order.orderDate}`);
      console.log(`  总金额: ${order.totalAmount}`);
      console.log(`  状态: ${order.status}`);
      console.log(`  备注: ${order.remark}`);
    });
    
    // 查询采购订单项数据
    console.log('\n=== 采购订单项数据 ===');
    const orderItems = await PurchaseOrderItem.findAll({
      include: [{
        model: PurchaseOrder,
        as: 'order',
        attributes: ['orderNumber']
      }],
      order: [['created_at', 'DESC']],
      limit: 10
    });
    
    console.log(`找到 ${orderItems.length} 条采购订单项记录:`);
    orderItems.forEach((item, index) => {
      console.log(`\n订单项 ${index + 1}:`);
      console.log(`  订单号: ${item.order ? item.order.orderNumber : 'N/A'}`);
      console.log(`  产品名称: ${item.productName}`);
      console.log(`  规格: ${item.specification}`);
      console.log(`  数量: ${item.quantity}`);
      console.log(`  单价: ${item.unitPrice}`);
      console.log(`  金额: ${item.amount}`);
      console.log(`  单位: ${item.unit}`);
    });
    
    // 统计信息
    console.log('\n=== 统计信息 ===');
    const orderCount = await PurchaseOrder.count();
    const orderItemCount = await PurchaseOrderItem.count();
    console.log(`采购订单总数: ${orderCount}`);
    console.log(`采购订单项总数: ${orderItemCount}`);
    
  } catch (error) {
    console.error('查询数据时出错:', error);
  } finally {
    await sequelize.close();
  }
}

// 执行查询
checkPurchaseData();